package swt.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;


import swt.entity.All;
import swt.entity.Sale;
import swt.util.DBHelp;
import swt.util.Page;
import swt.util.RowMapper;

public class SaleDao {
	private DBHelp<Sale> db = new DBHelp<Sale>();
	private DBHelp<All> allDb = new DBHelp<All>();
	
	public void add( String id ,String time, String comp_id, String tr_id, String address,
			float totalmoney, int penum, float unitmoney) {
		String sql = "insert into t_sale(id,time,comp_id,tr_id,address,totalmoney,penum,unitmoney) values(?,?,?,?,?,?,?,?)";
		db.executeSQL(sql,id, time, comp_id, tr_id, address, totalmoney, penum,
				unitmoney);
	}
	public int getTotalNums(){
		String sql="select count(*) from t_sale";
		return db.executeQueryForCount(sql);
	}
	public List<Sale> findAll() {
		String sql = "select id,time,comp_id,tr_id,`address`,totalmoney,penum,unitmoney from  t_sale ";
		return db.executeQueryForList(sql, new SaleRowMapper());
	}

	public Sale findById(String id) {
		String sql = "select id,time,comp_id,tr_id,`address`,totalmoney,penum,unitmoney from t_sale where id=?";
		return db.executeQueryForObject(sql, new SaleRowMapper(), id);
	}
	public List<All> findAlls() {
		String sql = "SELECT t_sale.id,t_sale.time,t_comp.compname,t_tr.trname,t_sale.address,"
				+ "t_sale.totalmoney,t_sale.unitmoney,t_sale.penum "
				+ "FROM t_sale INNER JOIN t_comp ON t_sale.comp_id = t_comp.id INNER JOIN t_tr "
				+ "ON t_sale.tr_id = t_tr.id";
		return allDb.executeQueryForList(sql, new AllRowMapper());
	}
	public List<All> findSearch(String where) {
		String sql = "SELECT t_sale.id,t_sale.time,t_comp.compname,t_tr.trname,t_sale.address,"
				+ "t_sale.totalmoney,t_sale.unitmoney,t_sale.penum "
				+ "FROM t_sale INNER JOIN t_comp ON t_sale.comp_id = t_comp.id INNER JOIN t_tr "
				+ "ON t_sale.tr_id = t_tr.id "+where;
		return allDb.executeQueryForList(sql, new AllRowMapper());
	}
	public Page<All> findByPage(Page<All> page) {
		String sql = "SELECT t_sale.id,t_sale.time,t_comp.compname,t_tr.trname,t_sale.address,"
				+ "t_sale.totalmoney,t_sale.unitmoney,t_sale.penum "
				+ "FROM t_sale INNER JOIN t_comp ON t_sale.comp_id = t_comp.id INNER JOIN t_tr "
				+ "ON t_sale.tr_id = t_tr.id limit ?,?";
		List<All> list =allDb.executeQueryForList(sql, new AllRowMapper(), page.getStartNum(),page.getPageSize());
		page.setResult(list);
		return page;
	}
	public void update(String time, int comp_id, int tr_id, String from,
			float totalmoney, int num, float unitmoney, int id) {
		String sql = "update t_sale set `time`=?,comp_id=?,tr_id=?,`from`=?,totalmoney=?,num=?, unitmoney=? where id=? ";
		db.executeSQL(sql, time, comp_id, tr_id, from, totalmoney, num,
				unitmoney, id);
	}
	public void del(String id) {
		String sql = "DELETE FROM t_sale WHERE `id`=?";
		db.executeSQL(sql, id);
	}
	private class AllRowMapper implements RowMapper<All> {

		public All mapperRow(ResultSet rs) throws SQLException {
			All all = new All();
			all.setId(rs.getString("id"));
			all.setCompanyname(rs.getString("compname"));
			all.setAddress(rs.getString("address"));
			all.setPenum(rs.getInt("penum"));
			all.setTime(rs.getString("time"));
			all.setTotalmoney(rs.getFloat("totalmoney"));
			all.setTrname(rs.getString("trname"));
			all.setUnitmoney(rs.getFloat("unitmoney"));
			return all;
		}

	}
	private class SaleRowMapper implements RowMapper<Sale> {

		public Sale mapperRow(ResultSet rs) throws SQLException {

			Sale sale = new Sale();
			sale.setId(rs.getString("id"));
			sale.setTime(rs.getString("time"));
			sale.setComp_id(rs.getString("comp_id"));
			sale.setTr_id(rs.getString("tr_id"));
			sale.setAddress(rs.getString("address"));
			sale.setTotalmoney(rs.getFloat("totalmoney"));

			sale.setPenum(rs.getInt("penum"));

			return sale;
		}

	}
	public List<All> findSerach(String where) {
		String sql = "SELECT t_sale.id,t_sale.time,t_comp.compname,t_tr.trname,t_sale.from,"
				+ "t_sale.totalmoney,t_sale.unitmoney,t_sale.num "
				+ "FROM t_sale INNER JOIN t_comp ON t_sale.comp_id = t_comp.id INNER JOIN t_tr "
				+ "ON t_sale.tr_id = t_tr.id " + where;
		return allDb.executeQueryForList(sql, new AllRowMapper());
	}

}
